package appIdeasScrumer.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import appIdeasScrumer.excepcion.DAOExcepcion;
import appIdeasScrumer.modelo.Reunion;
import appIdeasScrumer.util.ConexionBD;

public class ReunionDAO extends BaseDAO{
	
	public Reunion insertarReunion(Reunion vo) throws DAOExcepcion {
		String query = "";
		//String query = "insert into tc_reunion(COD_IDEA,COD_CALIFICACION,DES_OBSERVACION,AUD_FECREGISTRO,AUD_FECMODIFICACON,AUD_USUREGISTRO,AUD_USUMODIFICACION,AUD_ESTADOLOGICO) values (?,?)";
		
		query += "insert into tc_reunion(";
		query += "COD_IDEA,COD_CALIFICACION,DES_OBSERVACION, aud_fecregistro";	
		query += ") values(";
		query += "  ?, ?, ?, now() ";
		query += ")";
		
		//System.out.println(query);
		
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, vo.getCodIdea());
			stmt.setInt(2, vo.getCodCalificacion());
			stmt.setString(3, vo.getObservacion());
	
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			// Obtener el ultimo id
			int id = 0;
			query = "select last_insert_id()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				id = rs.getInt(1);
			}
			vo.setCodReunio(id);

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
			
	}

	
public void eliminarReunion(int CodReunion) throws DAOExcepcion{
		
		//Variables Globales	
		String query = "DELETE from tc_reunion  WHERE cod_reunion=?";
		Connection con = null;
		PreparedStatement stmt = null;
		
		try {
			//Instancia y Ejecuta 
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1,CodReunion);

			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
			
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

public Reunion obtenerReunion(int codReunion) throws DAOExcepcion {
	Reunion vo = new Reunion();
	Connection cnx = null;
	PreparedStatement stmt = null;
	ResultSet rs = null;
	try {
		String query = "select cod_reunion, cod_idea, cod_calificacion, des_observacion, aud_fecregistro, aud_fecmodificacion, aud_usuregistro, aud_usumodificacion, aud_estadologico  from tc_reunion where cod_reunion=?";
		cnx = ConexionBD.obtenerConexion();
		stmt = cnx.prepareStatement(query);
		stmt.setInt(1, codReunion);
		rs = stmt.executeQuery();
		if (rs.next()) {
			;
			vo.setCodReunio(rs.getInt(0));
			vo.setCodIdea(rs.getInt(1));
			vo.setCodCalificacion(rs.getInt(2));
			vo.setObservacion(rs.getString(3));
		
			vo.setAud_usuregistro(rs.getInt(6));
			vo.setAud_usumodificacion(rs.getInt(7));
			vo.setAud_estadologico(rs.getInt(8));
			
		}
	} catch (SQLException e) {
		System.err.println(e.getMessage());
		throw new DAOExcepcion(e.getMessage());
	} finally {
		this.cerrarResultSet(rs);
		this.cerrarStatement(stmt);
		this.cerrarConexion(cnx);
	}
	return vo;
}
	




public Collection<Reunion> listarReunion(int codIdea) throws DAOExcepcion {
	Collection<Reunion> r = new ArrayList<Reunion>();
	Connection cnx = null;
	PreparedStatement stmt = null;
	ResultSet rs = null;
	try {
		cnx = ConexionBD.obtenerConexion();
		String query = "select cod_reunion, cod_idea, cod_calificacion, (select nom_calificacion from tc_calificacion ca where ca.cod_calificacion = r.cod_calificacion) as des_calificacion, des_observacion, aud_fecregistro , aud_fecmodificacion, aud_usuregistro, aud_usumodificacion, aud_estadologico " +
						"from tc_reunion r where cod_idea = " + codIdea + " "+
						"order by cod_calificacion";
		stmt = cnx.prepareStatement(query);
		rs = stmt.executeQuery();
		while (rs.next()) {
			Reunion vo = new Reunion();
				
			vo.setCodReunio(rs.getInt("cod_reunion"));
			vo.setCodIdea(rs.getInt("cod_idea"));
			vo.setCodCalificacion(rs.getInt("cod_calificacion"));
			vo.setObservacion(rs.getString("Des_observacion"));
			
			vo.setDes_calificacion(rs.getString("des_calificacion"));
			
			vo.setAud_fecregistro(rs.getDate("aud_fecregistro"));
			
			r.add(vo);
		}

	} catch (SQLException e) {
		System.err.println(e.getMessage());
		throw new DAOExcepcion(e.getMessage());
	} finally {
		this.cerrarResultSet(rs);
		this.cerrarStatement(stmt);
		this.cerrarConexion(cnx);
	}
	return r;
}

}
